using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.MakeIT.Laundy.Agromotor.Site.Entidades;

namespace MakeIT.MakeIT.Laundy.Agromotor.Site.Negocios
{
	public partial class AgromotorsiteDAO : DAO
	{

		public static CategoriaVO getCategoria(int? idCategoria, string categoria)
		{
			CategoriaVO categoriaVO = null;
			List<CategoriaVO> listCategoriaVOs = getCategorias(idCategoria, categoria, null);
			if (listCategoriaVOs != null && listCategoriaVOs.Count > 0)
			{
				categoriaVO = listCategoriaVOs[0];
			}
			return categoriaVO;
		}

		public static CategoriaVO getCategoria(int? idCategoria)
		{
			return getCategoria(idCategoria, null);
		}

		public static List<CategoriaVO> getCategorias()
		{
			return getCategorias(null, null, null);
		}

		public static List<CategoriaVO> getCategorias(int? idCategoria)
		{
			return getCategorias(idCategoria, null, null);
		}

		public static List<CategoriaVO> getCategorias(int? idCategoria, string categoria, string chaves)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT cat.idCategoria as cat_idCategoria, cat.categoria as cat_categoria, cat.chaves as cat_chaves, cat.Cadastro as cat_Cadastro, cat.CadastradoPor as cat_CadastradoPor, cat.Edicao as cat_Edicao, cat.EditadoPor as cat_EditadoPor  ");
			query.AppendLine("FROM categoria as cat ");
			if (idCategoria != null)
			{
				query.AppendLine(where + "cat.idCategoria=@idCategoria ");
				where = "AND ";
			}
			if (categoria != null)
			{
				query.AppendLine(where + "cat.categoria LIKE @categoria ");
				where = "AND ";
			}
			if (chaves != null)
			{
                query.AppendLine(where + "cat.chaves LIKE @chaves ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY cat.categoria ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

            if (idCategoria != null) cmd.Parameters.Add(getParameter(cmd, "@idCategoria", idCategoria));
            if (categoria != null) cmd.Parameters.Add(getParameter(cmd, "@categoria", "%" + categoria + "%"));
            if (chaves != null) cmd.Parameters.Add(getParameter(cmd, "@chaves", "%|" + chaves + "|%"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<CategoriaVO> listCategoria = new List<CategoriaVO>();

				while (reader.Read())
				{
					CategoriaVO categoriaVO = CategoriaVO.CreateVO();
					categoriaVO.idCategoria = reader.GetInt32(reader.GetOrdinal("cat_idCategoria"));
					categoriaVO.categoria = reader.GetString(reader.GetOrdinal("cat_categoria"));
					categoriaVO.chaves = reader.GetString(reader.GetOrdinal("cat_chaves"));
					categoriaVO.cadastro = reader.GetDateTime(reader.GetOrdinal("cat_Cadastro"));
					categoriaVO.cadastradoPor = reader.GetString(reader.GetOrdinal("cat_CadastradoPor"));
					categoriaVO.edicao = reader.GetDateTime(reader.GetOrdinal("cat_Edicao"));
					categoriaVO.editadoPor = reader.GetString(reader.GetOrdinal("cat_EditadoPor"));


					listCategoria.Add(categoriaVO);
				}
				reader.Close();
				return listCategoria;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
        }

		public static void setCategoria(CategoriaVO categoriaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE categoria ");
			query.AppendLine("SET categoria=@categoria, chaves=@chaves,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idCategoria=@idCategoria ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCategoria", categoriaVO.idCategoria));
			cmd.Parameters.Add(getParameter(cmd, "@categoria", categoriaVO.categoria));
			cmd.Parameters.Add(getParameter(cmd, "@chaves", categoriaVO.chaves));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", categoriaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", categoriaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", categoriaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", categoriaVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newCategoria(CategoriaVO categoriaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO categoria ");
			query.AppendLine("  ( categoria, chaves, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @categoria, @chaves, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@categoria", categoriaVO.categoria));
			cmd.Parameters.Add(getParameter(cmd, "@chaves", categoriaVO.chaves));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", categoriaVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", categoriaVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", categoriaVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", categoriaVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('categoria') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delCategoria(CategoriaVO categoriaVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM categoria ");
			query.AppendLine("WHERE idCategoria=@idCategoria ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idCategoria", categoriaVO.idCategoria));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
	}
}
